<%@page import="schronisko.*"%>
<%@ page language="java" contentType="text/html; charset=ISO-8859-1"
    pageEncoding="ISO-8859-1"%>
<%@ page import="java.sql.*"%>
<!DOCTYPE html PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN" "http://www.w3.org/TR/html4/loose.dtd">
<html>
<head>
<meta http-equiv="Content-Type" content="text/html; charset=ISO-8859-1">
<title>Statystyki</title>
<style>
body{
	background: #c6b154;
}
</style>
<%
int[] dog = new int[12];
int[] cat = new int[12];
for(int i = 0;i<12;i++){ // iicjalizacja tablic
	dog[i] = 0;
	cat[i] = 0;
}
ResultSet r = null;
r = MySQLConnection.connect().executeQuery("SELECT month(add_date),race,count(race) from animal group by race, month(add_date)");
while(r.next()){
	if(r.getString(2).equals("pies")){
		dog[r.getInt(1)-1] = r.getInt(3);
	}
	else if(r.getString(2).equals("kot")){
		cat[r.getInt(1)-1] = r.getInt(3);
	}
}
MySQLConnection.Close();

%>
 <script type="text/javascript" src="https://www.google.com/jsapi"></script>
    <script type="text/javascript">
    
      google.load("visualization", "1", {packages:["corechart"]});
      google.setOnLoadCallback(drawChart);
      function drawChart() {
        var data = google.visualization.arrayToDataTable([
          ['Miesiac', 'Psy', 'Koty'],
          ['Styczen',  <%=dog[0] %>,      <%=cat[0] %>],
          ['Luty',  <%=dog[1] %>,      <%=cat[1] %>],
          ['Marzec',  <%=dog[2] %>,       <%=cat[2] %>],
          ['Kwiecien',  <%=dog[3] %>,       <%=cat[3] %>],
          ['Maj',  <%=dog[4] %>,       <%=cat[4] %>],
          ['Czerwiec',  <%=dog[5] %>,      <%=cat[5] %>],
          ['Lipiec',  <%=dog[6] %>,      <%=cat[6] %>],
          ['Sierpien',  <%=dog[7] %>,      <%=cat[7] %>],
          ['Wrzesien',  <%=dog[8] %>,      <%=cat[8] %>],
          ['Pazdziernik',  <%=dog[9] %>,      <%=cat[9] %>],
          ['Listopad',  <%=dog[10] %>,      <%=cat[10] %>],
          ['Grudzien',  <%=dog[11] %>,      <%=cat[11] %>]
        ]);

        var options = {
          title: 'Przygarniane w danym miesiacu',
          backgroundColor: '#c6b154',
          hAxis: {title: 'Miesiac', titleTextStyle: {color: 'red'}}
        };

        var chart = new google.visualization.ColumnChart(document.getElementById('chart_div'));
        chart.draw(data, options);
      }
    </script>
   <%
   int dog_age[] = new int[8];
   int cat_age[] = new int[8];
   for (int i = 0 ; i < 8; i++){
	   dog_age[i] = 0;
	   cat_age[i] = 0;
   }
   r = MySQLConnection.connect().executeQuery("SELECT race,count(year(date_of_birth)),year(CURDATE())-year(date_of_birth) from animal group by race, year(CURDATE())-year(date_of_birth)");
   while(r.next()){
	   if(r.getString(1).equals("pies")){
		   if(r.getInt(3) < 7)
		   dog_age[r.getInt(3)] = r.getInt(2);
		   else dog_age[7] += r.getInt(2);
	   }
	   else if(r.getString(1).equals("kot")){
		   if(r.getInt(3) < 7)
		   cat_age[r.getInt(3)] = r.getInt(2);
		   else cat_age[7] += r.getInt(2);
	   }
   }
   MySQLConnection.Close();
   %> 
 <script type="text/javascript" src="https://www.google.com/jsapi"></script>
    <script type="text/javascript">
      google.load("visualization", "1", {packages:["corechart"]});
      google.setOnLoadCallback(drawChart);
      function drawChart() {
        var data = google.visualization.arrayToDataTable([
          ['Wiek', 'Ilosc'],
          ['<1',     <%=dog_age[0]%>],
          ['1',      <%=dog_age[1]%>],
          ['2',  <%=dog_age[2]%>],
          ['3', <%=dog_age[3]%>],
          ['4',    <%=dog_age[4]%>],
          ['6',    <%=dog_age[5]%>],
          ['7',    <%=dog_age[6]%>],
          ['>7',    <%=dog_age[7]%>]
        ]);

        var options = {
          title: 'Wiek psow',
          backgroundColor: '#c6b154'
        };

        var chart = new google.visualization.PieChart(document.getElementById('piechart'));
        chart.draw(data, options);
      }
    </script>
    
    <script type="text/javascript" src="https://www.google.com/jsapi"></script>
    <script type="text/javascript">
      google.load("visualization", "1", {packages:["corechart"]});
      google.setOnLoadCallback(drawChart);
      function drawChart() {
        var data = google.visualization.arrayToDataTable([
          ['Wiek', 'Ilosc'],
          ['<1',     <%=cat_age[0]%>],
          ['1',      <%=cat_age[1]%>],
          ['2',  <%=cat_age[2]%>],
          ['3', <%=cat_age[3]%>],
          ['4',    <%=cat_age[4]%>],
          ['6',    <%=cat_age[5]%>],
          ['7',    <%=cat_age[6]%>],
          ['>7',    <%=cat_age[7]%>]
        ]);

        var options = {
          title: 'Wiek kotow',
          backgroundColor: '#c6b154'
        };

        var chart = new google.visualization.PieChart(document.getElementById('piechart1'));
        chart.draw(data, options);
      }
    </script>
    
</head>
<body>
<a href="index.jsp">Powrot na strone glowna</a>
	<br><br><br><center>
	<h1>eSchronisko w liczbach:</h1>
	<%
	int count_dog = 0;
	int count_cat = 0;
	int count_all = 0;
	r = MySQLConnection.connect().executeQuery("SELECT COUNT(ID_animal),race FROM animal GROUP BY race");
	while(r.next()){
		if(r.getString(2).equals("pies")){
			count_dog = r.getInt(1);
			count_all += r.getInt(1);
		}
		else if(r.getString(2).equals("kot")){
			count_cat = r.getInt(1);
			count_all += r.getInt(1);
		}
	}
	MySQLConnection.Close();
	int adopt_all = 0;
	int adopt_succ = 0;
	r = MySQLConnection.connect().executeQuery("SELECT COUNT(id_adoption),status FROM adoption GROUP BY status");
	while(r.next()){
		if(r.getString(2).equals("active")){
			adopt_succ = r.getInt(1);
		}
		adopt_all += r.getInt(1);
	}
	%>
	<h3>
	<table>
	<tr><td width=400>Liczba zwierzat ogolem</td><td><%=count_all %></td></tr>
	<tr><td>Liczba psow</td><td><%=count_dog %></td></tr>
	<tr><td>Liczba kotow</td><td><%=count_cat %></td></tr>
	<tr><td>Liczba adopcji zgloszonych</td><td><%=adopt_all %></td></tr>
	<tr><td>Liczba adopcji zakonczonych sukcesem</td><td><%=adopt_succ %></td></tr>
	</table>
	</h3>
	<h1>Wykresy:</h1>
<div class='content'>
<div id="chart_div" style="width: 1100px; height: 300px;"></div>
<div id="piechart" style="width: 600px; height: 300px;"></div><div id="piechart1" style="width: 600px; height: 300px;"></div>
</div>
</center>
</body>
</html>